package org.example.builder;


import com.alibaba.fastjson.JSON;
import javafx.scene.control.Tab;
import org.apache.commons.lang3.ArrayUtils;
import org.example.bean.Constants;
import org.example.bean.FiledInfo;
import org.example.bean.TableInfo;
import org.example.utils.JsonUtils;
import org.example.utils.PropertiesUtils;
import org.example.utils.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.util.ArrayList;
import java.util.Hashtable;
import java.util.List;


public class BuildTable {
    private static final Logger logger = LoggerFactory.getLogger( BuildTable.class);
    private static Connection conn = null;
    private static  String SQL_SHOW_TABLE_STATUS = "show table status";
    private static  String SQL_SHOW_TABLE_FIELDS = " show full fields from %s;";
    private static  String SQL_SHOW_TABLE_INDEX = " show index  from %s;";


    static {
        String dirverName = PropertiesUtils.getString( "db.driver.name");
        String url = PropertiesUtils.getString( "db.url");
        String user = PropertiesUtils.getString( "db.username");
        String password = PropertiesUtils.getString( "db.password");

        try {
            Class.forName(dirverName);
            conn = DriverManager.getConnection(url,user,password);
        } catch (Exception e) {
           logger.error("数据库链接失败！" ,e);
        }

    }

    public static List<TableInfo> getTables() {
        PreparedStatement ps = null;
        ResultSet tableResult = null;
        List<TableInfo > tableInfoList = new ArrayList<>();
         try{

            ps = conn.prepareStatement(SQL_SHOW_TABLE_STATUS);
            tableResult = ps.executeQuery();
            while (tableResult.next()) {
                String tableName = tableResult.getString("name");
                String comment = tableResult.getString("comment");
                //logger.info("tableName:{},comment:{}",tableName,comment);


                String beanName = tableName;
                if(Constants.IGNORE_TABLE_PREFIX) {
                    beanName = tableName.substring(beanName.indexOf("_") +1);
                }
                beanName = processFiled(beanName,true);

                TableInfo tableInfo = new TableInfo();
                tableInfo.setTableName(tableName);
                tableInfo.setBeanName(beanName);
                tableInfo.setComment(comment);
                tableInfo.setBeanParamName(beanName + Constants.SUFFIX_BEAN_QUERY);
              //  logger.info("表：{},备注:{},javabean:{},javaparambean：{}",tableInfo.getTableName(),
               //         tableInfo.getComment(),tableInfo.getBeanName(),tableInfo.getBeanParamName());
              readFieldInfo(tableInfo);
                //System.out.println(JsonUtils.convertObj2Json(filedInfoList));
                getKeyIndexInfo(tableInfo);
                //logger.info("table:{}",JsonUtils.convertObj2Json(tableInfo));

                tableInfoList.add(tableInfo);
            }


        } catch (Exception e) {
            logger.error("读取表失败！",e);
        } finally {
            if(tableResult != null) {
                try {
                    tableResult.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
            if(ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
            if(conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
        }
         return tableInfoList;
    }

    private static String processFiled(String field, Boolean upperCaseFirstLetter) {
        StringBuffer sb = new StringBuffer();
        String[] fields = field.split("_");
        sb.append(upperCaseFirstLetter ? StringUtils.upperCaseFirstLetter(fields[0]) : fields[0]);

        for (int i = 1,len = fields.length; i < len;i++) {
            sb.append(StringUtils.upperCaseFirstLetter(fields[i]));
        }

        return sb.toString();
    }

    private static void readFieldInfo(TableInfo tableInfo) {
        PreparedStatement ps = null;
        ResultSet fieldResult = null;
        List<FiledInfo > filedInfoList = new ArrayList<>();
        List<FiledInfo > fieldExtendList = new ArrayList<>();
        try{

            ps = conn.prepareStatement(String.format(SQL_SHOW_TABLE_FIELDS,tableInfo.getTableName()));
            fieldResult = ps.executeQuery();
            Boolean haveDate = false;
            Boolean haveDateTime = false;
            Boolean haveBigDecimal = false;
            while (fieldResult.next()) {
                String field = fieldResult.getString("field");
                String type = fieldResult.getString("type");
                String extra = fieldResult.getString("extra");
                String comment = fieldResult.getString("comment");

                //logger.info("tableName:{},comment:{}",tableName,comment);

                if(type.indexOf("(") > 0) {
                    type = type.substring(0,type.indexOf("("));
                }

                String propertyName = processFiled(field,false);
                FiledInfo filedInfo = new FiledInfo();
                filedInfoList.add(filedInfo);



                filedInfo.setComment(comment);
                filedInfo.setFileName(field);
                filedInfo.setSqlType(type);
                filedInfo.setAutoIncrement("auto_increment".equalsIgnoreCase(extra) ? true:false);
                filedInfo.setPropertyName(propertyName);
                filedInfo.setJavaType(processJavaType(type));
                //logger.info("field:{},propertyName:{},type:{},extra:{},comment:{}",field,propertyName,type,extra,comment);

                //logger.info("javatype:{}",filedInfo.getJavaType());

                if(ArrayUtils.contains(Constants.SQL_DATE_TIME_TYPES,type)) {
                    haveDateTime = true;
                }
                if(ArrayUtils.contains(Constants.SQL_DATE_TYPES,type)) {
                    haveDate = true;
                }
                if(ArrayUtils.contains(Constants.SQL_DECIMAL_TYPE,type)) {
                    haveBigDecimal = true;
                }
                //String类型的属性加fuzzy 扩展属性
                if (ArrayUtils.contains(Constants.SQL_STRING_TYPE,type)) {
                    String fuzzyFieldName = filedInfo.getPropertyName()+Constants.SUFFIX_BEAN_QUERY_FUZZY;


                    FiledInfo fuzzyField = new FiledInfo();
                    fuzzyField.setJavaType(filedInfo.getJavaType());
                    fuzzyField.setPropertyName(fuzzyFieldName);
                    fuzzyField.setSqlType(filedInfo.getSqlType());

                    fuzzyField.setFileName(filedInfo.getFileName());

                    fieldExtendList.add(fuzzyField);
                }
                //日期类型的属性加 start end
                if (ArrayUtils.contains(Constants.SQL_DATE_TIME_TYPES,type) ||
                        ArrayUtils.contains(Constants.SQL_DATE_TYPES,type)) {

                    String propertyStart = filedInfo.getPropertyName()+Constants.SUFFIX_BEAN_QUERY_TIME_START;
                    String propertyEnd = filedInfo.getPropertyName()+Constants.SUFFIX_BEAN_QUERY_TIME_END;

                    FiledInfo startField = new FiledInfo();
                    startField.setPropertyName(propertyStart);
                    startField.setFileName(filedInfo.getFileName());
                    startField.setSqlType(filedInfo.getSqlType());

                    startField.setJavaType("String");

                    FiledInfo endField = new FiledInfo();
                    endField.setPropertyName(propertyEnd);
                    endField.setFileName(filedInfo.getFileName());
                    endField.setSqlType(filedInfo.getSqlType());
                    endField.setJavaType("String");

                    fieldExtendList.add(startField);
                    fieldExtendList.add(endField);
                }

            }
            tableInfo.setHaveBigDecimal(haveBigDecimal);
            tableInfo.setHaveDate(haveDate);
            tableInfo.setHaveDateTime(haveDateTime);
            tableInfo.setFiledList(filedInfoList);
            tableInfo.setFiledExtendsList(fieldExtendList);

        } catch (Exception e) {
            logger.error("读取表失败！",e);
        } finally {
            if(fieldResult != null) {
                try {
                    fieldResult.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
            if(ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }

        }

    }


    private static List<FiledInfo> getKeyIndexInfo(TableInfo tableInfo) {
        PreparedStatement ps = null;
        ResultSet fieldResult = null;
        List<FiledInfo > filedInfoList = new ArrayList<>();
        try{
            Hashtable<String, FiledInfo> tempMap = new Hashtable<>();
            for (FiledInfo filedInfo: tableInfo.getFiledList()) {
                tempMap.put(filedInfo.getFileName(),filedInfo);
            }
            ps = conn.prepareStatement(String.format(SQL_SHOW_TABLE_INDEX,tableInfo.getTableName()));
            fieldResult = ps.executeQuery();
            while (fieldResult.next()) {
                String keyName = fieldResult.getString("key_name");
                Integer nonUnique = fieldResult.getInt("non_unique");
                String columnName = fieldResult.getString("column_name");
                //logger.info("tableName:{},comment:{}",tableName,comment);

                if(nonUnique == 1) {
                    continue;
                }
                List<FiledInfo> keyFieldList = tableInfo.getKeyIndexMap().get(keyName);
                if(null ==keyFieldList) {
                    keyFieldList = new ArrayList();
                    tableInfo.getKeyIndexMap().put(keyName,keyFieldList);
                }

                keyFieldList.add(tempMap.get(columnName));



            }

        } catch (Exception e) {
            logger.error("读取索引失败！",e);
        } finally {
            if(fieldResult != null) {
                try {
                    fieldResult.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
            if(ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }

        }
        return filedInfoList;
    }

    private static String processJavaType(String type) {

        if(ArrayUtils.contains(Constants.SQL_INTEGER_TYPE,type)) {
            return "Integer";
        } else if (ArrayUtils.contains(Constants.SQL_LONG_TYPES,type)) {
            return "Long";
        }else if (ArrayUtils.contains(Constants.SQL_STRING_TYPE,type)) {
            return "String";
        }else if (ArrayUtils.contains(Constants.SQL_DATE_TIME_TYPES,type) || ArrayUtils.contains(Constants.SQL_DATE_TYPES,type)) {
            return "Date";
        }else if (ArrayUtils.contains(Constants.SQL_DECIMAL_TYPE,type)) {
            return "BigDecimal";
        }else {
            throw new RuntimeException("无法识别的类型：" + type);
        }

    }
}
